﻿#region "Imports"
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using Practico2.DataTypes;

#endregion

namespace Practico2.DataAccess
{

    public class PaisDataAccess
    {

        public static int InsertPais(DTPais toInsert)
        {
            int toReturn = 0;
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            SqlDataReader reader = null;
            string insertCommandText = "INSERT INTO Pais([NombrePais],[AbreviacionPais]) VALUES (@NombrePais,@AbreviacionPais) ; SELECT @@Identity";
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }

            SqlCommand command;


            if (useTransaction)
            {
                command = new SqlCommand(insertCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(insertCommandText, connection);
            }

            if (toInsert.IsNombrePaisNull)
            {
                command.Parameters.Add(new SqlParameter("@NombrePais", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@NombrePais", toInsert.NombrePais));
            }
            if (toInsert.IsAbreviacionPaisNull)
            {
                command.Parameters.Add(new SqlParameter("@AbreviacionPais", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@AbreviacionPais", toInsert.AbreviacionPais));
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }

                reader = command.ExecuteReader();
                reader.Read();
                toReturn = Convert.ToInt32(reader.GetValue(0));
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }

                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static void UpdatePais(DTPais toUpdate)
        {
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string updateCommandText = "UPDATE Pais SET [NombrePais]=@NombrePais,[AbreviacionPais]=@AbreviacionPais WHERE [IdPais]=@IdPais";

            SqlCommand command;
            if (useTransaction)
            {
                command = new SqlCommand(updateCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(updateCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdPais", toUpdate.IdPais));

            if (toUpdate.IsNombrePaisNull)
            {
                command.Parameters.Add(new SqlParameter("@NombrePais", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@NombrePais", toUpdate.NombrePais));
            }
            if (toUpdate.IsAbreviacionPaisNull)
            {
                command.Parameters.Add(new SqlParameter("@AbreviacionPais", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@AbreviacionPais", toUpdate.AbreviacionPais));
            }


            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();

            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }

        }

        public static void DeletePais(Int32 idPais)
        {

            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            string deleteCommandText = "DELETE FROM Pais WHERE [IdPais]=@IdPais";

            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(deleteCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(deleteCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdPais", idPais));



            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }

        }

        public static DTPais GetPais(Int32 idPais)
        {

            string selectCommandText = String.Format("SELECT {0} FROM [Pais] WHERE [IdPais]=@IdPais", GetPaisFields());
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdPais", idPais));


            DTPais current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetPaisInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;
        }

        public static List<DTPais> ListPais()
        {

            List<DTPais> toReturn = new List<DTPais>();
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string selectCommandText = String.Format("SELECT {0} FROM [Pais]", GetPaisFields());
            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DTPais current = GetPaisInfo(reader, 0);
                    toReturn.Add(current);
                }
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static string GetPaisFields()
        {
            return " [Pais].[IdPais],[Pais].[NombrePais],[Pais].[AbreviacionPais] ";
        }

        public static DTPais GetPaisInfo(SqlDataReader reader, int index)
        {
            DTPais current = new DTPais();
            object temp;

            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.IdPais = (Int32)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.NombrePais = (String)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.AbreviacionPais = (String)temp;
            }
            index++;


            return current;
        }


        public static DTPais GetPaisPorNombre(string nombre)
        {
            string selectCommandText = String.Format("SELECT {0} FROM [Pais] WHERE [NombrePais]=@NombrePais", GetPaisFields());
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@NombrePais", nombre));


            DTPais current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetPaisInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;

        }

        /// <summary>
        /// Dado el nombre exacto de un pais devuelve una instancia de pais con todos los datos o null
        /// sino existe
        /// </summary>
        /// <param name="countryName"></param>
        /// <returns></returns>
        public static DTPais ObtenerPaisSegunNombre(string countryName)
        {
            string selectCommandText = String.Format("SELECT {0} FROM [Pais] WHERE [nombrePais]='{1}'", GetPaisFields(), countryName);
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            DTPais current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetPaisInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;
        }
    }
}